package com.etonenet.repository.system;

import java.math.BigDecimal;
import java.util.List;

import org.springframework.data.jpa.repository.Query;

import com.etonenet.entity.system.SystemNode;

import esms.etonenet.common.repository.BaseRepository;

public interface SystemNodeRepository extends BaseRepository<SystemNode, Long> {

	@Deprecated
	@Query(nativeQuery = true, value = "SELECT * FROM TS_SYSTEM_NODE START WITH PARENT_ID is null CONNECT BY PRIOR SYSTEM_NODE_ID=PARENT_ID")
	List<SystemNode> listAllTreegrid();

	@Query(nativeQuery=true,value="SELECT * FROM TS_SYSTEM_NODE where FIND_IN_SET(SYSTEM_NODE_ID, getChildList(?1))")
	List<SystemNode> listAllTreegridBySystemNodeID(int systemNodeId);
	
	@Query(nativeQuery=true,value="select SYSTEM_NODE_ID from TS_SYSTEM_NODE where PARENT_ID is null ORDER BY DISPLAY_ORDER")
	List<Integer> listAllRootNode();
	
	@Query(nativeQuery = true, value = "SELECT * FROM TS_SYSTEM_NODE where PARENT_ID = ?1 and node_type=0 order by display_order asc")
	List<SystemNode> listChildren(Long parentId);

	@Query(nativeQuery = true, value = "SELECT count(*) FROM TS_SYSTEM_NODE where PARENT_ID = ?1 and node_type=0")
	BigDecimal countChildren(Long parentId);

	@Query(nativeQuery = true, value = "SELECT * FROM TS_SYSTEM_NODE where PARENT_ID is null and node_type=0 order by display_order asc")
	List<SystemNode> listAllRoot();

	@Query(nativeQuery = true, value = "SELECT * FROM TS_SYSTEM_NODE WHERE URL = ?1")
	SystemNode findNodeByUrl(String url);
}
